package com.habdqn.studentmanager.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.function.Consumer;

import org.apache.commons.lang3.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.habdqn.studentmanager.exception.StudentException;

/**
 * @author Fan Excel工具类 2017年6月6日
 * 
 */
public class ExcelUtil {

	private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

	private ExcelUtil() {

	}

	public static class Excel {

		/**
		 * 要被处理的excel
		 */
		private XSSFWorkbook excel;

		/**
		 * 记录第几页sheet, 默认从 0 开始
		 */
		private int sheetAt = 0;

		/**
		 * 记录从第几条读取, 默认从 0 开始
		 */
		private int rowAt = 0;

		public Excel(XSSFWorkbook excel) {
			this.excel = excel;
		}

		/**
		 * 设置要处理的sheet
		 * 
		 * @param num
		 *            默认从 0 开始
		 * @return
		 */
		public Excel sheetAt(int num) {
			this.sheetAt = num < 0 ? 0 : num;
			return this;
		}

		/**
		 * 设置从第几行开始处理
		 * 
		 * @param num
		 *            默认从 0 开始
		 * @return
		 */
		public Excel rowAt(int num) {
			this.rowAt = num < 0 ? 0 : num;
			return this;
		}
		
		/**
		 * 关闭资源
		 */
		public void close() {
			try {
				this.excel.close();
			}
			catch (IOException e) {
				logger.error("释放excel资源失败", e);
			}
		}
		
		/**
		 * 封装 XSSFRow
		 * 
		 * @author Fan
		 * 
		 *         2017年6月6日
		 * 
		 */
		public static class Row {

			/**
			 * 保存 XSSFRow
			 */
			private XSSFRow row;

			/**
			 * 当前实例对应的行数, 从 0 开始
			 */
			private int rowAt;

			public Row(XSSFRow row, int rowAt) {
				this.row = row;
				this.rowAt = rowAt;
			}

			/**
			 * 获取指定列
			 * 
			 * @param num
			 * @return
			 */
			public Cell getCell(int num) {
				XSSFCell cell = row.getCell(num);
				return new Cell(cell, num);
			}

			/**
			 * 创建一个新的Cell
			 * 
			 * @param num
			 * @return
			 */
			public Cell createCell(int num) {
				CellStyle cellStyle = row.getSheet().getWorkbook().createCellStyle();
				// 居中样式
				cellStyle.setAlignment(HorizontalAlignment.CENTER);

				XSSFCell xssfCell = row.createCell(num, CellType.STRING);
				xssfCell.setCellStyle(cellStyle);
				return new Cell(xssfCell, num);
			}

			/**
			 * 获取对应的行号
			 * 
			 * @return
			 */
			public int getRowAt() {
				return rowAt;
			}

			/**
			 * 返回总列数
			 * 
			 * @return
			 */
			public int getCellCount() {
				return row.getLastCellNum();
			}
		}

		/**
		 * 封装 XSSFCell
		 * 
		 * @author Fan
		 * 
		 *         2017年6月7日
		 * 
		 */
		public static class Cell {

			/**
			 * 保存 XSSFRow
			 */
			private XSSFCell cell;

			/**
			 * 当前实例对应的列数, 从 1 开始
			 */
			private int cellAt = 1;

			public Cell(XSSFCell cell, int cellAt) {
				this.cell = cell;
				this.cellAt = cellAt;
			}

			/**
			 * 返回 Cell 中的值
			 * 
			 * @return
			 */
			public String getValue() {

				if (null == cell) {
					return null;
				}
				String value = null;

				switch (cell.getCellTypeEnum()) {
				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", LocaleUtil.getUserLocale());
						sdf.setTimeZone(LocaleUtil.getUserTimeZone());
						return sdf.format(cell.getDateCellValue());
					}
					DecimalFormat df = new DecimalFormat("#.##");
					value = df.format(getNumericCellValue());
					break;
				case STRING:
					value = cell.getRichStringCellValue().toString().trim();
					break;
				case FORMULA:
					value = cell.getCellFormula();
					break;
				case BLANK:
					value = "";
					break;
				case BOOLEAN:
					value = cell.getBooleanCellValue() ? "TRUE" : "FALSE";
					break;
				case ERROR:
					value = ErrorEval.getText(cell.getErrorCellValue());
					break;
				default:
					value = "Unknown Cell Type: " + cell.getCellTypeEnum();
				}
				
				return StringEscapeUtils.escapeHtml4(value);
			}

			/**
			 * 获取Cell中的数值
			 * 
			 * @return
			 */
			public double getNumericCellValue() {
				return cell.getNumericCellValue();
			}

			/**
			 * 设置 Cell 中的值
			 * 
			 * @param value
			 */
			public void setValue(String value) {
				cell.setCellValue(value);
			}

			/**
			 * 获取对应的列号
			 * 
			 * @return
			 */
			public int getCellAt() {
				return cellAt;
			}
		}

		/**
		 * 开始处理并接收每行的数据
		 * 
		 * @param consumer
		 * @return
		 */
		public Excel accept(Consumer<Row> consumer) {
			XSSFSheet sheet = excel.getSheetAt(sheetAt);

			int end = sheet.getLastRowNum();

			for (int i = rowAt; i <= end; i++) {
				XSSFRow xssfRow = sheet.getRow(i);

				if (null == xssfRow) {
					continue;
				}

				Row row = new Row(xssfRow, i);
				consumer.accept(row);
			}
			
			return this;
		}

		/**
		 * 创建一个Sheet
		 * 
		 * @param name
		 * @return
		 */
		public Excel createSheet(String name) {

			if (null == name || StringUtils.isEmpty(name)) {
				excel.createSheet();
			}
			else {
				excel.createSheet(name);
			}
			return this;
		}
		
		/**
		 * 将数据导出到内存中, 用于处理多Sheet的情况, 
		 * <p><b>需要在导出完毕后执行export(out)将数据写入到流中</b></p>
		 * 
		 * @param list
		 *            要导出的集合
		 * @param consumer
		 *            处理每条数据该如何导出
		 * @param Excel
		 * 			  返回自身
		 */
		public <T> Excel export(List<T> list, Function<T, Row> consumer) {
			if (null == list || list.isEmpty()) {
				return this;
			}
			
			XSSFSheet sheet = excel.getSheetAt(sheetAt);
			for (int i = 0; i < list.size(); i++) {
				T t = list.get(i);

				if (null == t) {
					continue;
				}

				int rowNum = i + rowAt;
				XSSFRow xssfRow = sheet.createRow(rowNum);
				Row row = new Row(xssfRow, rowNum);
				consumer.apply(t, row);
			}
			
			return this;
		}
		
		/**
		 * 导出excel
		 * 
		 * @param list
		 *            要导出的集合
		 * @param out
		 *            要导出到的输出流
		 * @param consumer
		 *            处理每条数据该如何导出
		 */
		public <T> Excel export(List<T> list, OutputStream out, Function<T, Row> consumer) {
			if (null == list || list.isEmpty()) {
				return this;
			}

			XSSFSheet sheet = excel.getSheetAt(sheetAt);
			for (int i = 0; i < list.size(); i++) {
				T t = list.get(i);

				if (null == t) {
					continue;
				}

				int rowNum = i + rowAt;
				XSSFRow xssfRow = sheet.createRow(rowNum);
				Row row = new Row(xssfRow, rowNum);
				consumer.apply(t, row);
			}

			try {
				excel.write(out);
			}
			catch (IOException e) {
				logger.error("导出excel失败", e);
				throw new StudentException("写出数据失败", e);
			}
			
			return this;
		}
		
		/**
		 * 导出内存中的数据到输出流中
		 * 
		 * @param out 要导出到的输出流
		 */
		public <T> Excel export(OutputStream out) {
			
			try {
				excel.write(out);
			}
			catch (IOException e) {
				logger.error("导出excel失败", e);
				throw new StudentException("写出数据失败", e);
			}
			
			return this;
		}
	}

	/**
	 * 初始化
	 * 
	 * @param is
	 * @return
	 * @throws IOException
	 */
	public static Excel init(InputStream is) {
		try {
			XSSFWorkbook excel = new XSSFWorkbook(is);
			return new Excel(excel);
		}
		catch (IOException e) {
			logger.error("初始化excel失败", e);
			throw new StudentException("读取excel失败", e);
		}
	}

	/**
	 * 初始化一个空的excel
	 * 
	 * @return
	 */
	public static Excel init() {
		XSSFWorkbook excel = new XSSFWorkbook();
		return new Excel(excel);
	}
}